package com.lerning.edu.manage.controller;

import com.lerning.edu.commons.util.DateUtil;
import com.lerning.edu.commons.util.StringUtil;
import com.lerning.edu.services.CampusDSRByJMSService;
import com.lerning.edu.services.CampusDSRService;
import com.lerning.edu.services.CampusDsrNewService;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.shiro.authz.annotation.RequiresPermissions;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.math.BigDecimal;
import java.text.DecimalFormat;
import java.text.NumberFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.HashSet;
import java.util.List;
import java.util.Map;

/**
 * @author jiangwei
 * @since 18/7/19
 */
@Controller
@RequestMapping("campus")
public class CampusDsrNewController {

    @Autowired
    private CampusDsrNewService campusDsrNewService;
    @Autowired
    private CampusDSRService campusDSRService;
    @Autowired
    private CampusDSRByJMSService campusDSRByJMSService;

    DecimalFormat df = new DecimalFormat("######0");
    DecimalFormat df1 = new DecimalFormat("######0.00");

    NumberFormat nf = new DecimalFormat("￥,###.##");

    @RequestMapping("/dsrByUser")
    @RequiresPermissions("dsrByUser:view")
    public String dsrByUser(HttpServletRequest request,
                            @RequestParam(value = "CampusID", required = false) String CampusID,
                            @RequestParam(value = "AutoID", required = false) String AutoID) {

        String TIN = "27F05A82-5601-45B2-83A5-8360A641F41F,65C4B6C0-465E-427B-96BB-85225704BA9D";
        String TOUT = "FBE4B6F5-E5C9-4D36-BBB2-F31EE975F076";
        String WalkIN = "EADC6B6D-4E9F-4DB2-B8DC-0739F72E2507";
        String REF = "A0D6D64E-8018-4766-82A3-1A41578A7F38";

        String BeginDate = "";
        String EndDate = "";

        List tinList = new ArrayList();
        List toutList = new ArrayList();
        List winList = new ArrayList();
        List refList = new ArrayList();

        List tinByUser = new ArrayList();
        List toutByUser = new ArrayList();
        List winByUser = new ArrayList();
        List refByUser = new ArrayList();

        List tinByUserFPShow = new ArrayList();
        List tinByUserOCShow = new ArrayList();

        List toutByUserFPShow = new ArrayList();
        List toutByUserOCShow = new ArrayList();

        List winByUserFPShow = new ArrayList();
        List winByUserOCShow = new ArrayList();

        List refByUserFPShow = new ArrayList();
        List refByUserOCShow = new ArrayList();

        List tinEnByUser = new ArrayList();
        List toutEnByUser = new ArrayList();
        List winEnByUser = new ArrayList();
        List refEnByUser = new ArrayList();

        List enByUserSummary = new ArrayList();

        List departs = campusDSRService.queryDepartList();//校区
        List FinanceMonths = campusDSRService.queryNaturalMonths();//财务月列表

        if (StringUtil.isEmpty(AutoID)) {
            List FinanceMonthOfDq = campusDSRService.queryNatureMonthOfDq(DateUtil.dayMap());//当前月

            Map dateMap = (HashMap) FinanceMonthOfDq.get(0);
            AutoID = dateMap.get("AutoID").toString();
        } else {
            Map beginDateAndEndDate = campusDSRService.queryNaturalMonth(Integer.parseInt(AutoID));//财务月开始和结束时间

            BeginDate = beginDateAndEndDate.get("BeginDate").toString().substring(0, 10);
            EndDate = beginDateAndEndDate.get("EndDate").toString().substring(0, 10);
            Map map = new HashMap();
            map.put("CampusID", CampusID);
            map.put("BeginDate", BeginDate);
            map.put("EndDate", EndDate);

            map.put("FBeginDate", beginDateAndEndDate.get("BeginDate").toString());
            map.put("FEndDate", beginDateAndEndDate.get("EndDate").toString());

            map.put("SaleModeIds", TIN);
            tinByUser = campusDsrNewService.queryByUserForApp(map);
            tinByUserFPShow = campusDsrNewService.queryByUserOfFPShowByTinTout(map);
            tinByUserOCShow = campusDsrNewService.queryByUserOfOCShowByTinTout(map);

            if(Integer.valueOf(AutoID) > 8){
                tinEnByUser = campusDsrNewService.queryEnByUserNew(map);
            }else {
                tinEnByUser = campusDsrNewService.queryEnByUser(map);
            }

            map.put("SaleModeIds", TOUT);
            toutByUser = campusDsrNewService.queryByUserForApp(map);
            toutByUserFPShow = campusDsrNewService.queryByUserOfFPShowByTinTout(map);
            toutByUserOCShow = campusDsrNewService.queryByUserOfOCShowByTinTout(map);

            if(Integer.valueOf(AutoID) > 8){
                toutEnByUser = campusDsrNewService.queryEnByUserNew(map);
            }else {
                toutEnByUser = campusDsrNewService.queryEnByUser(map);
            }

            map.put("SaleModeIds", WalkIN);
            winByUser = campusDsrNewService.queryByUser(map);
            winByUserFPShow = campusDsrNewService.queryByUserOfFPShowByWalkInRef(map);
            winByUserOCShow = campusDsrNewService.queryByUserOfOCShowByWalkInRef(map);

            if(Integer.valueOf(AutoID) > 8){
                winEnByUser = campusDsrNewService.queryEnByUserNew(map);
            }else {
                winEnByUser = campusDsrNewService.queryEnByUser(map);
            }

            map.put("SaleModeIds", REF);
            refByUser = campusDsrNewService.queryByUser(map);
            refByUserFPShow = campusDsrNewService.queryByUserOfFPShowByWalkInRef(map);
            refByUserOCShow = campusDsrNewService.queryByUserOfOCShowByWalkInRef(map);

            if(Integer.valueOf(AutoID) > 8){
                refEnByUser = campusDsrNewService.queryEnByUserNew(map);
            }else {
                refEnByUser = campusDsrNewService.queryEnByUser(map);
            }

            //
            Map ebsMap = new HashMap();
            ebsMap.put("BeginDate", beginDateAndEndDate.get("BeginDate").toString());
            ebsMap.put("EndDate", beginDateAndEndDate.get("EndDate").toString());
            ebsMap.put("CampusID", CampusID);
            ebsMap.put("text", EndDate.substring(0, 7));

            if(Integer.valueOf(AutoID) > 8){
                enByUserSummary = campusDsrNewService.queryEnByUserSummaryNew(ebsMap);
            }else {
                enByUserSummary = campusDsrNewService.queryEnByUserSummary(ebsMap);
            }

            List ebs = new ArrayList();
            if (enByUserSummary.size() > 0) {
                for (Object obj : enByUserSummary) {
                    Map objMap = (HashMap) obj;
                    double totalEn = Double.valueOf(objMap.get("totalEn").toString());
                    if (totalEn > 0) {
                        ebs.add(objMap);
                    }
                }
            }

            enByUserSummary = ebs;

            //合并app ,FreshPresShow,OCShow和En
            tinList = getCampusOfUser(tinByUser, CampusID, TIN, BeginDate, EndDate, "app", tinByUserFPShow, tinByUserOCShow, tinEnByUser);
            toutList = getCampusOfUser(toutByUser, CampusID, TOUT, BeginDate, EndDate, "app", toutByUserFPShow, toutByUserOCShow, toutEnByUser);
            winList = getCampusOfUser(winByUser, CampusID, WalkIN, BeginDate, EndDate, "leads", winByUserFPShow, winByUserOCShow, winEnByUser);
            refList = getCampusOfUser(refByUser, CampusID, REF, BeginDate, EndDate, "leads", refByUserFPShow, refByUserOCShow, refEnByUser);
        }

        request.setAttribute("AutoID", AutoID);
        request.setAttribute("FinanceMonths", FinanceMonths);

        request.setAttribute("departs", departs);
        request.setAttribute("CampusID", CampusID);

        StringUtil.sortByWhatAsc(tinList, "cField1");
        StringUtil.sortByWhatAsc(toutList, "cField1");
        StringUtil.sortByWhatAsc(winList, "cField1");
        StringUtil.sortByWhatAsc(refList, "cField1");

        request.setAttribute("tinByUser", ratio(tinList));
        request.setAttribute("toutByUser", ratio(toutList));
        request.setAttribute("winByUser", ratio(winList));
        request.setAttribute("refByUser", ratio(refList));
        request.setAttribute("renewByUser", refEnByUser);

        List ebsList = new ArrayList();
        if(refEnByUser.size() >0){
            for (Object obj : refEnByUser){
                Map ebsMap = (HashMap)obj;
                ebsMap.put("CashIn", Double.valueOf(ebsMap.get("TCashIn").toString()));
                ebsMap.put("CashIn1", nf.format(Double.valueOf(ebsMap.get("TCashIn").toString())));
                ebsMap.put("CashIn2", nf.format(Double.valueOf(ebsMap.get("TCashIn2").toString())));
                ebsList.add(ebsMap);
            }
        }

        refEnByUser = ebsList;

        request.setAttribute("enByUserSummary", enByUserSummary);

        request.setAttribute("appSumTIN", sum(tinList, CampusID, TIN, BeginDate, EndDate, "app"));
        request.setAttribute("appSumTOUT", sum(toutList, CampusID, TOUT, BeginDate, EndDate, "app"));
        request.setAttribute("appSumWKIN", sum(winList, CampusID, WalkIN, BeginDate, EndDate, "leads"));
        request.setAttribute("appSumRef", sum(refList, CampusID, REF, BeginDate, EndDate, "leads"));
        request.setAttribute("renewByUserTotal", renewByUserTotal(refEnByUser));

        request.setAttribute("ebsTotal", ebsTotal(enByUserSummary));

        return "DRS/byUser";
    }

    @RequestMapping("/dsrByUserByJMS")
    public String dsrByUserByJMS(HttpServletRequest request,
                            @RequestParam(value = "CampusID", required = false) String CampusID,
                            @RequestParam(value = "companyID", required = false) String companyID,
                            @RequestParam(value = "AutoID", required = false) String AutoID) {

        Map campusMap = new HashMap();
        campusMap.put("CampusID", CampusID);
        campusMap.put("companyID", companyID);

        List saleMode = campusDSRByJMSService.querySaleModeByCampus(campusMap);

        String TIN = "";
        String TOUT = "";
        String WalkIN = "";
        String REF = "";

        if(saleMode.size() > 0){
            for (Object obj : saleMode){
                Map objMap  = (HashMap)obj;
                if(objMap.get("cName").equals("Tin")){
                    TIN = objMap.get("cID").toString();
                }else if(objMap.get("cName").equals("T-out")){
                    TOUT =objMap.get("cID").toString();
                }else if(objMap.get("cName").equals("Ref")){
                    REF = objMap.get("cID").toString();
                }else if(objMap.get("cName").equals("Walk-in")){
                    if(!StringUtil.isEmpty(WalkIN)){
                        WalkIN = WalkIN +","+ objMap.get("cID").toString();
                    }else {
                        WalkIN = objMap.get("cID").toString();

                    }
                }
            }
        }

        String BeginDate = "";
        String EndDate = "";

        List tinList = new ArrayList();
        List toutList = new ArrayList();
        List winList = new ArrayList();
        List refList = new ArrayList();

        List tinByUser = new ArrayList();
        List toutByUser = new ArrayList();
        List winByUser = new ArrayList();
        List refByUser = new ArrayList();

        List tinByUserFPShow = new ArrayList();
        List tinByUserOCShow = new ArrayList();

        List toutByUserFPShow = new ArrayList();
        List toutByUserOCShow = new ArrayList();

        List winByUserFPShow = new ArrayList();
        List winByUserOCShow = new ArrayList();

        List refByUserFPShow = new ArrayList();
        List refByUserOCShow = new ArrayList();

        List tinEnByUser = new ArrayList();
        List toutEnByUser = new ArrayList();
        List winEnByUser = new ArrayList();
        List refEnByUser = new ArrayList();

        List enByUserSummary = new ArrayList();

        List FinanceMonths = campusDSRService.queryNaturalMonths();//财务月列表

        if (StringUtil.isEmpty(AutoID)) {
            List FinanceMonthOfDq = campusDSRService.queryNatureMonthOfDq(DateUtil.dayMap());//当前月

            Map dateMap = (HashMap) FinanceMonthOfDq.get(0);
            AutoID = dateMap.get("AutoID").toString();
        } else {
            Map beginDateAndEndDate = campusDSRService.queryNaturalMonth(Integer.parseInt(AutoID));//财务月开始和结束时间

            BeginDate = beginDateAndEndDate.get("BeginDate").toString().substring(0, 10);
            EndDate = beginDateAndEndDate.get("EndDate").toString().substring(0, 10);
            Map map = new HashMap();
            map.put("CampusID", CampusID);
            map.put("BeginDate", BeginDate);
            map.put("EndDate", EndDate);

            map.put("FBeginDate", beginDateAndEndDate.get("BeginDate").toString());
            map.put("FEndDate", beginDateAndEndDate.get("EndDate").toString());
            map.put("companyID", companyID);

            map.put("SaleModeIds", TIN);
            tinByUser = campusDSRByJMSService.queryByUserForApp(map);
            tinByUserFPShow = campusDSRByJMSService.queryByUserOfFPShowByTinTout(map);
            tinByUserOCShow = campusDSRByJMSService.queryByUserOfOCShowByTinTout(map);

            if(Integer.valueOf(AutoID) > 8){
                tinEnByUser = campusDSRByJMSService.queryEnByUserNew(map);
            }else {
                tinEnByUser = campusDSRByJMSService.queryEnByUser(map);
            }

            map.put("SaleModeIds", TOUT);
            toutByUser = campusDSRByJMSService.queryByUserForApp(map);
            toutByUserFPShow = campusDSRByJMSService.queryByUserOfFPShowByTinTout(map);
            toutByUserOCShow = campusDSRByJMSService.queryByUserOfOCShowByTinTout(map);

            if(Integer.valueOf(AutoID) > 8){
                toutEnByUser = campusDSRByJMSService.queryEnByUserNew(map);
            }else {
                toutEnByUser = campusDSRByJMSService.queryEnByUser(map);
            }

            map.put("SaleModeIds", WalkIN);
            winByUser = campusDSRByJMSService.queryByUser(map);
            winByUserFPShow = campusDSRByJMSService.queryByUserOfFPShowByWalkInRef(map);
            winByUserOCShow = campusDSRByJMSService.queryByUserOfOCShowByWalkInRef(map);

            if(Integer.valueOf(AutoID) > 8){
                winEnByUser = campusDSRByJMSService.queryEnByUserNew(map);
            }else {
                winEnByUser = campusDSRByJMSService.queryEnByUser(map);
            }

            map.put("SaleModeIds", REF);
            refByUser = campusDSRByJMSService.queryByUser(map);
            refByUserFPShow = campusDSRByJMSService.queryByUserOfFPShowByWalkInRef(map);
            refByUserOCShow = campusDSRByJMSService.queryByUserOfOCShowByWalkInRef(map);

            if(Integer.valueOf(AutoID) > 8){
                refEnByUser = campusDSRByJMSService.queryEnByUserNew(map);
            }else {
                refEnByUser = campusDSRByJMSService.queryEnByUser(map);
            }

            //
            Map ebsMap = new HashMap();
            ebsMap.put("BeginDate", beginDateAndEndDate.get("BeginDate").toString());
            ebsMap.put("EndDate", beginDateAndEndDate.get("EndDate").toString());
            ebsMap.put("CampusID", CampusID);
            ebsMap.put("text", EndDate.substring(0, 7));
            ebsMap.put("companyID", companyID);


            if(Integer.valueOf(AutoID) > 8){
                enByUserSummary = campusDSRByJMSService.queryEnByUserSummaryNew(ebsMap);
            }else {
                enByUserSummary = campusDSRByJMSService.queryEnByUserSummary(ebsMap);
            }

            List ebs = new ArrayList();
            if (enByUserSummary.size() > 0) {
                for (Object obj : enByUserSummary) {
                    Map objMap = (HashMap) obj;
                    double totalEn = Double.valueOf(objMap.get("totalEn").toString());
                    if (totalEn > 0) {
                        ebs.add(objMap);
                    }
                }
            }

            enByUserSummary = ebs;

            //合并app ,FreshPresShow,OCShow和En
            tinList = getCampusOfUserJMS(tinByUser, CampusID, TIN, BeginDate, EndDate, "app", tinByUserFPShow, tinByUserOCShow, tinEnByUser);
            toutList = getCampusOfUserJMS(toutByUser, CampusID, TOUT, BeginDate, EndDate, "app", toutByUserFPShow, toutByUserOCShow, toutEnByUser);
            winList = getCampusOfUserJMS(winByUser, CampusID, WalkIN, BeginDate, EndDate, "leads", winByUserFPShow, winByUserOCShow, winEnByUser);
            refList = getCampusOfUserJMS(refByUser, CampusID, REF, BeginDate, EndDate, "leads", refByUserFPShow, refByUserOCShow, refEnByUser);
        }

        request.setAttribute("AutoID", AutoID);
        request.setAttribute("FinanceMonths", FinanceMonths);

        request.setAttribute("CampusID", CampusID);
        request.setAttribute("companyID", companyID);

        StringUtil.sortByWhatAsc(tinList, "cField1");
        StringUtil.sortByWhatAsc(toutList, "cField1");
        StringUtil.sortByWhatAsc(winList, "cField1");
        StringUtil.sortByWhatAsc(refList, "cField1");

        request.setAttribute("tinByUser", ratio(tinList));
        request.setAttribute("toutByUser", ratio(toutList));
        request.setAttribute("winByUser", ratio(winList));
        request.setAttribute("refByUser", ratio(refList));
        request.setAttribute("renewByUser", refEnByUser);

        List ebsList = new ArrayList();
        if(refEnByUser.size() >0){
            for (Object obj : refEnByUser){
                Map ebsMap = (HashMap)obj;
                ebsMap.put("CashIn", Double.valueOf(ebsMap.get("TCashIn").toString()));
                ebsMap.put("CashIn1", nf.format(Double.valueOf(ebsMap.get("TCashIn").toString())));
                ebsMap.put("CashIn2", nf.format(Double.valueOf(ebsMap.get("TCashIn2").toString())));
                ebsList.add(ebsMap);
            }
        }

        refEnByUser = ebsList;

        request.setAttribute("enByUserSummary", enByUserSummary);

        request.setAttribute("appSumTIN", sumJMS(tinList, CampusID, TIN, BeginDate, EndDate, "app"));
        request.setAttribute("appSumTOUT", sumJMS(toutList, CampusID, TOUT, BeginDate, EndDate, "app"));
        request.setAttribute("appSumWKIN", sumJMS(winList, CampusID, WalkIN, BeginDate, EndDate, "leads"));
        request.setAttribute("appSumRef", sumJMS(refList, CampusID, REF, BeginDate, EndDate, "leads"));
        request.setAttribute("renewByUserTotal", renewByUserTotal(refEnByUser));

        request.setAttribute("ebsTotal", ebsTotal(enByUserSummary));

        return "DRS/byUserByJMS";
    }

    public Map sum(List list, String cID, String SaleModeIds, String BeginDate, String EndDate, String type) {
        Map returnMap = new HashMap();
        BigDecimal sumApp = new BigDecimal(0);
        BigDecimal sumFP = new BigDecimal(0);
        BigDecimal sumOC = new BigDecimal(0);

        BigDecimal sumContracts = new BigDecimal(0);
        BigDecimal sumCashIn = new BigDecimal(0);

        if (list.size() > 0) {
            for (Object obj : list) {
                Map map = (HashMap) obj;

                String CampusID = map.get("cLevelString").toString();
                CampusID = CampusID.replace("|", "");

                //是本校区的人,数据直接相加
                if (cID.equals(CampusID)) {
                    sumApp = sumApp.add(new BigDecimal(map.get("app").toString()));
                    sumFP = sumFP.add(new BigDecimal(map.get("fp").toString()));
                    sumOC = sumOC.add(new BigDecimal(map.get("oc1").toString()));
                    sumContracts = sumContracts.add(new BigDecimal(map.get("Contracts1").toString()));
                    sumCashIn = sumCashIn.add(new BigDecimal(map.get("CashIn").toString()));
                } else {//非同一个校区
                    Map countMap = new HashMap();
                    countMap.put("cField1", map.get("cField1"));
                    countMap.put("CampusID", cID);
                    countMap.put("SaleModeIds", SaleModeIds);
                    countMap.put("BeginDate", BeginDate);
                    countMap.put("EndDate", EndDate);

                    int countApp = 0;
                    if (type.equals("app")) {
                        countApp = campusDsrNewService.queryByUserForAppCount(countMap);//app数据
                    } else {
                        countApp = campusDsrNewService.queryByUserForLeadsCount(countMap);//leads数
                    }

                    int customerOfCampusCount = campusDsrNewService.queryCustomerOfCampusCount(countMap);//所属校区数

                    if (countApp > 0 && customerOfCampusCount == 1) {
                        sumApp = sumApp.add(new BigDecimal(map.get("app").toString()));
                        sumFP = sumFP.add(new BigDecimal(map.get("fp").toString()));
                        sumOC = sumOC.add(new BigDecimal(map.get("oc").toString()));
                        sumContracts = sumContracts.add(new BigDecimal(map.get("Contracts1").toString()));
                        sumCashIn = sumCashIn.add(new BigDecimal(map.get("CashIn").toString()));
                    }
                }
            }
        }

        returnMap.put("sumApp", sumApp);
        returnMap.put("sumFP", sumFP);
        returnMap.put("sumOC", sumOC);

        if (!StringUtil.divideValue(sumApp)) {
            returnMap.put("sumShowRatio", 0);
        } else {
            returnMap.put("sumShowRatio", df.format(sumFP.divide(sumApp, 2, BigDecimal.ROUND_HALF_UP).doubleValue() * 100));
        }

        returnMap.put("sumContracts", sumContracts);
        returnMap.put("sumCashIn", nf.format(sumCashIn));

        if (!StringUtil.divideValue(sumContracts)) {
            returnMap.put("sumRatio", 0.00);
        } else {
            returnMap.put("sumRatio", sumFP.divide(sumContracts, 2, BigDecimal.ROUND_HALF_UP));
        }

        return returnMap;
    }

    public Map sumJMS(List list, String cID, String SaleModeIds, String BeginDate, String EndDate, String type) {
        Map returnMap = new HashMap();
        BigDecimal sumApp = new BigDecimal(0);
        BigDecimal sumFP = new BigDecimal(0);
        BigDecimal sumOC = new BigDecimal(0);

        BigDecimal sumContracts = new BigDecimal(0);
        BigDecimal sumCashIn = new BigDecimal(0);

        if (list.size() > 0) {
            for (Object obj : list) {
                Map map = (HashMap) obj;

                String CampusID = map.get("cLevelString").toString();
                CampusID = CampusID.replace("|", "");

                //是本校区的人,数据直接相加
                if (cID.equals(CampusID)) {
                    sumApp = sumApp.add(new BigDecimal(map.get("app").toString()));
                    sumFP = sumFP.add(new BigDecimal(map.get("fp").toString()));
                    sumOC = sumOC.add(new BigDecimal(map.get("oc1").toString()));
                    sumContracts = sumContracts.add(new BigDecimal(map.get("Contracts1").toString()));
                    sumCashIn = sumCashIn.add(new BigDecimal(map.get("CashIn").toString()));
                } else {//非同一个校区
                    Map countMap = new HashMap();
                    countMap.put("cField1", map.get("cField1"));
                    countMap.put("CampusID", cID);
                    countMap.put("SaleModeIds", SaleModeIds);
                    countMap.put("BeginDate", BeginDate);
                    countMap.put("EndDate", EndDate);

                    int countApp = 0;
                    if (type.equals("app")) {
                        countApp = campusDSRByJMSService.queryByUserForAppCount(countMap);//app数据
                    } else {
                        countApp = campusDSRByJMSService.queryByUserForLeadsCount(countMap);//leads数
                    }

                    int customerOfCampusCount = campusDSRByJMSService.queryCustomerOfCampusCount(countMap);//所属校区数

                    if (countApp > 0 && customerOfCampusCount == 1) {
                        sumApp = sumApp.add(new BigDecimal(map.get("app").toString()));
                        sumFP = sumFP.add(new BigDecimal(map.get("fp").toString()));
                        sumOC = sumOC.add(new BigDecimal(map.get("oc").toString()));
                        sumContracts = sumContracts.add(new BigDecimal(map.get("Contracts1").toString()));
                        sumCashIn = sumCashIn.add(new BigDecimal(map.get("CashIn").toString()));
                    }
                }
            }
        }

        returnMap.put("sumApp", sumApp);
        returnMap.put("sumFP", sumFP);
        returnMap.put("sumOC", sumOC);

        if (!StringUtil.divideValue(sumApp)) {
            returnMap.put("sumShowRatio", 0);
        } else {
            returnMap.put("sumShowRatio", df.format(sumFP.divide(sumApp, 2, BigDecimal.ROUND_HALF_UP).doubleValue() * 100));
        }

        returnMap.put("sumContracts", sumContracts);
        returnMap.put("sumCashIn", nf.format(sumCashIn));

        if (!StringUtil.divideValue(sumContracts)) {
            returnMap.put("sumRatio", 0.00);
        } else {
            returnMap.put("sumRatio", sumFP.divide(sumContracts, 2, BigDecimal.ROUND_HALF_UP));
        }

        return returnMap;
    }

    public Map renewByUserTotal(List list) {
        Map returnMap = new HashMap();
        BigDecimal sumTC = new BigDecimal(0);
        BigDecimal sumTC2 = new BigDecimal(0);
        BigDecimal sumTCI = new BigDecimal(0);
        BigDecimal sumTCI2 = new BigDecimal(0);

        if (list.size() > 0) {
            for (Object obj : list) {
                Map map = (HashMap) obj;

                sumTC = sumTC.add(new BigDecimal(map.get("TContracts").toString()));
                sumTC2 = sumTC2.add(new BigDecimal(map.get("TContracts2").toString()));
                sumTCI = sumTCI.add(new BigDecimal(map.get("TCashIn").toString()));
                sumTCI2 = sumTCI2.add(new BigDecimal(map.get("TCashIn2").toString()));
            }
        }

        returnMap.put("sumTC", sumTC);
        returnMap.put("sumTC2", sumTC2);
        returnMap.put("sumTCI", nf.format(sumTCI));
        returnMap.put("sumTCI2", nf.format(sumTCI2));

        return returnMap;
    }

    public List getCampusOfUser(List list, String cID, String SaleModeIds, String BeginDate, String EndDate, String type, List fpShow, List ocShow, List enByUser) {
        List returnList = new ArrayList();

        List fpList = new ArrayList();
        List ocList = new ArrayList();

        if (list.size() > 0) {
            for (Object obj : list) {
                Map map = (HashMap) obj;

                map.put("CashInValue", nf.format(map.get("CashIn")));
                map.put("oc1", map.get("oc"));
                map.put("Contracts1", map.get("Contracts"));

                BigDecimal fp = new BigDecimal(map.get("fp").toString());
                BigDecimal app = new BigDecimal(map.get("app").toString());

                String CampusID = map.get("cLevelString").toString();
                CampusID = CampusID.replace("|", "");

                if (!StringUtil.isEmpty(CampusID)) {
                    Map tDepart = campusDsrNewService.queryCampus(CampusID);
                    map.put("userCampus", tDepart.get("cName"));
                    map.put("userCampusID", tDepart.get("cID"));

                    if (!StringUtil.divideValue(app)) {
                        map.put("ShowRatio", 0);
                    } else {
                        map.put("ShowRatio", df.format(fp.divide(app, 2, BigDecimal.ROUND_HALF_UP).doubleValue() * 100));
                    }

                    if (cID.equals(CampusID)) {
                        returnList.add(map);
                        fpList.add(map);
                    } else {
                        Map countMap = new HashMap();
                        countMap.put("cField1", map.get("cField1"));
                        countMap.put("CampusID", cID);
                        countMap.put("SaleModeIds", SaleModeIds);
                        countMap.put("BeginDate", BeginDate);
                        countMap.put("EndDate", EndDate);

                        int countApp = 0;

                        if (type.equals("app")) {
                            countApp = campusDsrNewService.queryByUserForAppCount(countMap);
                        } else {
                            countApp = campusDsrNewService.queryByUserForLeadsCount(countMap);
                        }

                        int customerOfCampusCount = campusDsrNewService.queryCustomerOfCampusCount(countMap);

                        if (countApp > 0 && customerOfCampusCount == 1) {
                            returnList.add(map);
                            fpList.add(map);
                        }
                    }
                }

                //不在app里的员工,并且fp show不为0 (FP)
                if (fpShow.size() > 0) {
                    String cName = map.get("cField1").toString();//app
                    for (Object objApp : fpShow) {
                        Map mapApp = (HashMap) objApp;
                        String cNameFP = mapApp.get("cField1").toString();//pfShow
                        String fpCampusID = mapApp.get("cLevelString").toString();
                        fpCampusID = fpCampusID.replace("|", "");

                        int fpShowCount = (int) mapApp.get("fp");

                        if (!cName.equals(cNameFP) && cID.equals(fpCampusID) && fpShowCount > 0) {
                            mapApp.put("app", 0);
                            mapApp.put("oc", 0);
                            mapApp.put("oc1", 0);
                            mapApp.put("Contracts1", 0);
                            mapApp.put("Contracts", 0.00);
                            mapApp.put("CashIn", 0.00);
                            mapApp.put("CashInValue", nf.format(0));
                            mapApp.put("ShowRatio", 0);
                            returnList.add(mapApp);
                            fpList.add(mapApp);
                        }
                    }
                }
            }

            if (fpList.size() == 0) {
                fpList = list;
            }
            //不在app里的员工,并且OC show不为0 (OC)
            if (fpList.size() > 0 && ocShow.size() > 0) {
                for (Object objFP : fpList) {
                    Map fpMap = (HashMap) objFP;
                    String cName = fpMap.get("cField1").toString();//fp
                    for (Object objOC : ocShow) {
                        Map mapOC = (HashMap) objOC;
                        String cNameFP = mapOC.get("cField1").toString();//pfShow

                        String ocCampusID = mapOC.get("cLevelString").toString();
                        ocCampusID = ocCampusID.replace("|", "");

                        int oc = (int) mapOC.get("oc");

                        if (!cName.equals(cNameFP) && cID.equals(ocCampusID) && oc > 0) {
                            mapOC.put("app", 0);
                            mapOC.put("fp", 0);
                            mapOC.put("oc1", oc);
                            mapOC.put("Contracts1", 0);
                            mapOC.put("Contracts", 0.00);
                            mapOC.put("CashIn", 0.00);
                            mapOC.put("CashInValue", nf.format(0));
                            mapOC.put("ShowRatio", 0);
                            returnList.add(mapOC);
                            ocList.add(mapOC);
                        }
                    }
                }
            }

            if (ocList.size() == 0) {
                ocList = fpList;
            }
            //不在app里的员工,并且有En (En)
            if (ocList.size() > 0) {
                for (Object objOC : ocList) {
                    Map ocMap = (HashMap) objOC;
                    String cName = ocMap.get("cField1").toString();//oc
                    for (Object objEn : enByUser) {
                        Map mapEn = (HashMap) objEn;
                        String cNameEn = mapEn.get("cField1").toString();//En

                        String enCampusID = mapEn.get("cLevelString").toString();
                        enCampusID = enCampusID.replace("|", "");

                        Double Contracts = Double.valueOf(mapEn.get("Contracts").toString());

                        if (!cName.equals(cNameEn) && Contracts > 0 && cID.equals(enCampusID)) {
                            mapEn.put("app", 0);
                            mapEn.put("fp", 0);
                            mapEn.put("oc", 0);
                            mapEn.put("oc1", 0);
                            mapEn.put("ShowRatio", 0);
                            mapEn.put("Contracts1", Contracts);
                            mapEn.put("CashIn1", mapEn.get("CashIn"));
                            mapEn.put("CashInValue", nf.format(mapEn.get("CashIn")));
                            returnList.add(mapEn);
                        }
                    }
                }
            }
        }

        //去重
        HashSet h = new HashSet(returnList);
        returnList.clear();
        returnList.addAll(h);

        return removeDuplicate(returnList);
    }

    public List getCampusOfUserJMS(List list, String cID, String SaleModeIds, String BeginDate, String EndDate, String type, List fpShow, List ocShow, List enByUser) {
        List returnList = new ArrayList();

        List fpList = new ArrayList();
        List ocList = new ArrayList();

        if (list.size() > 0) {
            for (Object obj : list) {
                Map map = (HashMap) obj;

                map.put("CashInValue", nf.format(map.get("CashIn")));
                map.put("oc1", map.get("oc"));
                map.put("Contracts1", map.get("Contracts"));

                BigDecimal fp = new BigDecimal(map.get("fp").toString());
                BigDecimal app = new BigDecimal(map.get("app").toString());

                String CampusID = map.get("cLevelString").toString();
                CampusID = CampusID.replace("|", "");

                if (!StringUtil.isEmpty(CampusID)) {
                    Map tDepart = campusDSRByJMSService.queryCampus(CampusID);
                    map.put("userCampus", tDepart.get("cName"));
                    map.put("userCampusID", tDepart.get("cID"));

                    if (!StringUtil.divideValue(app)) {
                        map.put("ShowRatio", 0);
                    } else {
                        map.put("ShowRatio", df.format(fp.divide(app, 2, BigDecimal.ROUND_HALF_UP).doubleValue() * 100));
                    }

                    if (cID.equals(CampusID)) {
                        returnList.add(map);
                        fpList.add(map);
                    } else {
                        Map countMap = new HashMap();
                        countMap.put("cField1", map.get("cField1"));
                        countMap.put("CampusID", cID);
                        countMap.put("SaleModeIds", SaleModeIds);
                        countMap.put("BeginDate", BeginDate);
                        countMap.put("EndDate", EndDate);

                        int countApp = 0;

                        if (type.equals("app")) {
                            countApp = campusDSRByJMSService.queryByUserForAppCount(countMap);
                        } else {
                            countApp = campusDSRByJMSService.queryByUserForLeadsCount(countMap);
                        }

                        int customerOfCampusCount = campusDSRByJMSService.queryCustomerOfCampusCount(countMap);

                        if (countApp > 0 && customerOfCampusCount == 1) {
                            returnList.add(map);
                            fpList.add(map);
                        }
                    }
                }

                //不在app里的员工,并且fp show不为0 (FP)
                if (fpShow.size() > 0) {
                    String cName = map.get("cField1").toString();//app
                    for (Object objApp : fpShow) {
                        Map mapApp = (HashMap) objApp;
                        String cNameFP = mapApp.get("cField1").toString();//pfShow
                        String fpCampusID = mapApp.get("cLevelString").toString();
                        fpCampusID = fpCampusID.replace("|", "");

                        int fpShowCount = (int) mapApp.get("fp");

                        if (!cName.equals(cNameFP) && cID.equals(fpCampusID) && fpShowCount > 0) {
                            mapApp.put("app", 0);
                            mapApp.put("oc", 0);
                            mapApp.put("oc1", 0);
                            mapApp.put("Contracts1", 0);
                            mapApp.put("Contracts", 0.00);
                            mapApp.put("CashIn", 0.00);
                            mapApp.put("CashInValue", nf.format(0));
                            mapApp.put("ShowRatio", 0);
                            returnList.add(mapApp);
                            fpList.add(mapApp);
                        }
                    }
                }
            }

            if (fpList.size() == 0) {
                fpList = list;
            }
            //不在app里的员工,并且OC show不为0 (OC)
            if (fpList.size() > 0 && ocShow.size() > 0) {
                for (Object objFP : fpList) {
                    Map fpMap = (HashMap) objFP;
                    String cName = fpMap.get("cField1").toString();//fp
                    for (Object objOC : ocShow) {
                        Map mapOC = (HashMap) objOC;
                        String cNameFP = mapOC.get("cField1").toString();//pfShow

                        String ocCampusID = mapOC.get("cLevelString").toString();
                        ocCampusID = ocCampusID.replace("|", "");

                        int oc = (int) mapOC.get("oc");

                        if (!cName.equals(cNameFP) && cID.equals(ocCampusID) && oc > 0) {
                            mapOC.put("app", 0);
                            mapOC.put("fp", 0);
                            mapOC.put("oc1", oc);
                            mapOC.put("Contracts1", 0);
                            mapOC.put("Contracts", 0.00);
                            mapOC.put("CashIn", 0.00);
                            mapOC.put("CashInValue", nf.format(0));
                            mapOC.put("ShowRatio", 0);
                            returnList.add(mapOC);
                            ocList.add(mapOC);
                        }
                    }
                }
            }

            if (ocList.size() == 0) {
                ocList = fpList;
            }
            //不在app里的员工,并且有En (En)
            if (ocList.size() > 0) {
                for (Object objOC : ocList) {
                    Map ocMap = (HashMap) objOC;
                    String cName = ocMap.get("cField1").toString();//oc
                    for (Object objEn : enByUser) {
                        Map mapEn = (HashMap) objEn;
                        String cNameEn = mapEn.get("cField1").toString();//En

                        String enCampusID = mapEn.get("cLevelString").toString();
                        enCampusID = enCampusID.replace("|", "");

                        Double Contracts = Double.valueOf(mapEn.get("Contracts").toString());

                        if (!cName.equals(cNameEn) && Contracts > 0 && cID.equals(enCampusID)) {
                            mapEn.put("app", 0);
                            mapEn.put("fp", 0);
                            mapEn.put("oc", 0);
                            mapEn.put("oc1", 0);
                            mapEn.put("ShowRatio", 0);
                            mapEn.put("Contracts1", Contracts);
                            mapEn.put("CashIn1", mapEn.get("CashIn"));
                            mapEn.put("CashInValue", nf.format(mapEn.get("CashIn")));
                            returnList.add(mapEn);
                        }
                    }
                }
            }
        }

        //去重
        HashSet h = new HashSet(returnList);
        returnList.clear();
        returnList.addAll(h);

        return removeDuplicate(returnList);
    }

    public List ratio(List list) {
        List valueList = new ArrayList();

        if (list.size() > 0) {
            for (Object obj : list) {
                Map map = (HashMap) obj;
                BigDecimal fp = new BigDecimal(map.get("fp").toString());
                BigDecimal en = new BigDecimal(map.get("Contracts1").toString());

                if (StringUtil.divideValue(en.multiply(new BigDecimal("2")))) {
                    map.put("Ratio", fp.divide(en, 2, BigDecimal.ROUND_HALF_UP));
                } else {
                    map.put("Ratio", 0.00);
                }
                valueList.add(map);
            }
        }

        return valueList;
    }

    /***
     * 深度去重
     *
     * @param list
     * @return
     */
    public List removeDuplicate(List list) {
        List byList = new ArrayList();
        for (int i = 0; i < list.size() - 1; i++) {
            for (int j = list.size() - 1; j >= 0; j--) {
                if (j != i) {

                    Map map = (HashMap) list.get(i);
                    int appi = (int) map.get("app");
                    int oci = (int) map.get("oc");
                    String cField1 = map.get("cField1").toString();
                    double iContracts = Double.valueOf(map.get("Contracts").toString());

                    Map jMap = (HashMap) list.get(j);
                    int app = (int) jMap.get("app");
                    int oc = (int) jMap.get("oc");
                    String cField = jMap.get("cField1").toString();
                    double Contracts = Double.valueOf(jMap.get("Contracts").toString());

                    if (cField.equals(cField1)) {//两条数据的员工编号相同,说明数据重复

                        if (app == 0 && appi == 0 && oci == 0 && oc == 0) {
                            if (iContracts > 0) {
                                byList.add(list.get(i));
                                jMap.put("Contracts1", map.get("Contracts"));
                                jMap.put("CashIn", map.get("CashIn"));
                                jMap.put("CashInValue", nf.format(map.get("CashIn")));
                            } else if (Contracts > 0 ) {
                                byList.add(list.get(j));
                                map.put("Contracts1", jMap.get("Contracts"));
                                map.put("CashIn", jMap.get("CashIn"));
                                map.put("CashInValue", nf.format(jMap.get("CashIn")));
                            }
                        } else if (app == 0 && appi != 0) {//如果外层list的app不为0,而里层app为0,则将里层数据添加到 将要删除的list中
                            byList.add(list.get(j));
                        } else if (app != 0 && appi == 0) {//如果外层list的app为0,而里层app不为0,则将外层数据添加到 将要删除的list中
                            byList.add(list.get(i));
                        } else if (app == 0 && appi == 0) {//如果两个数据的app都为0,则将oc不为0的数据添加到 将要删除的list中,并将oc的值赋给oc值为0的key为oc1的数据
                            if (oci != 0) {//如果外层oc值不为0,,则将外层oc的值赋给里层的oc1
                                byList.add(list.get(i));
                                jMap.put("oc1", map.get("oc"));
                            } else if (oc != 0) {//如果里层oc值不为0,,则将里层oc的值赋给外层的oc1
                                byList.add(list.get(j));
                                map.put("oc1", jMap.get("oc"));
                            }
                        }
                    }
                }
            }
        }

        list.removeAll(byList);

        return list;
    }

    //个人业绩达成汇总
    public Map ebsTotal(List list) {
        Map mapValue = new HashMap();

        BigDecimal renewEn = new BigDecimal(0);
        BigDecimal renewEn2 = new BigDecimal(0);
        BigDecimal renewKpi = new BigDecimal(0);

        BigDecimal refEn = new BigDecimal(0);
        BigDecimal refKpi = new BigDecimal(0);

        BigDecimal mktEn = new BigDecimal(0);
        BigDecimal mktKpi = new BigDecimal(0);

        BigDecimal totalEn = new BigDecimal(0);
        BigDecimal totalKpi = new BigDecimal(0);

        if (list.size() > 0) {
            for (Object obj : list) {
                Map map = (HashMap) obj;
                renewEn = renewEn.add(new BigDecimal(map.get("renewEn").toString()));
                renewEn2 = renewEn2.add(new BigDecimal(map.get("renewEn2").toString()));
                renewKpi = renewKpi.add(new BigDecimal(map.get("renewKpi").toString()));

                refEn = refEn.add(new BigDecimal(map.get("refEn").toString()));
                refKpi = refKpi.add(new BigDecimal(map.get("refKpi").toString()));

                mktEn = mktEn.add(new BigDecimal(map.get("mktEn").toString()));
                mktKpi = mktKpi.add(new BigDecimal(map.get("mktKpi").toString()));

                totalEn = totalEn.add(new BigDecimal(map.get("totalEn").toString()));
                totalKpi = totalKpi.add(new BigDecimal(map.get("totalKpi").toString()));
            }

            mapValue.put("renewEn", renewEn);
            mapValue.put("renewEn2", renewEn2);
            mapValue.put("renewKpi", renewKpi);

            if (StringUtil.divideValue(renewKpi)) {
                BigDecimal TRenew = renewEn.add(renewEn2);
                mapValue.put("renewRatio", df1.format(TRenew.divide(renewKpi, 4, BigDecimal.ROUND_HALF_UP).doubleValue() * 100));
            } else {
                mapValue.put("renewRatio", 0.00);
            }

            mapValue.put("refEn", refEn);
            mapValue.put("refKpi", refKpi);

            if (StringUtil.divideValue(refKpi)) {
                mapValue.put("refRatio", df1.format(refEn.divide(refKpi, 4, BigDecimal.ROUND_HALF_UP).doubleValue() * 100));
            } else {
                mapValue.put("refRatio", 0.00);
            }

            mapValue.put("mktEn", mktEn);
            mapValue.put("mktKpi", mktKpi);

            if (StringUtil.divideValue(mktKpi)) {
                mapValue.put("mktRatio", df1.format(mktEn.divide(mktKpi, 4, BigDecimal.ROUND_HALF_UP).doubleValue() * 100));
            } else {
                mapValue.put("mktRatio", 0.00);
            }

            mapValue.put("totalEn", totalEn);
            mapValue.put("totalKpi", totalKpi);

            if (StringUtil.divideValue(totalKpi)) {
                mapValue.put("totalRatio", df1.format(totalEn.divide(totalKpi, 4, BigDecimal.ROUND_HALF_UP).doubleValue() * 100));
            } else {
                mapValue.put("totalRatio", 0.00);
            }

        } else {
            mapValue.put("renewEn", 0.00);
            mapValue.put("renewEn2", 0.00);
            mapValue.put("renewKpi", 0.00);

            mapValue.put("renewRatio", 0.00);

            mapValue.put("refEn", 0.00);
            mapValue.put("refKpi", 0.00);
            mapValue.put("refRatio", 0.00);

            mapValue.put("mktEn", 0.00);
            mapValue.put("mktKpi", 0.00);
            mapValue.put("mktRatio", 0.00);

            mapValue.put("totalEn", 0.00);
            mapValue.put("totalKpi", 0.00);
            mapValue.put("totalRatio", 0.00);
        }

        return mapValue;
    }

    @RequestMapping("/export")
    public void exportDsrByUserSummaryDetail(HttpServletResponse response,
                          @RequestParam(value = "AutoID", required = false) String AutoID,
                          @RequestParam(value = "CampusID", required = false) String CampusID,
                          @RequestParam(value = "cField1", required = false) String cField1) throws Exception {

        String MKT = "27F05A82-5601-45B2-83A5-8360A641F41F,65C4B6C0-465E-427B-96BB-85225704BA9D,FBE4B6F5-E5C9-4D36-BBB2-F31EE975F076,EADC6B6D-4E9F-4DB2-B8DC-0739F72E2507,559FF86A-AC29-46FE-84AD-CE774B8EFB79";
        String REF = "A0D6D64E-8018-4766-82A3-1A41578A7F38";

        Map beginDateAndEndDate = campusDSRService.queryNaturalMonth(Integer.parseInt(AutoID));//财务月开始和结束时间

        Map map = new HashMap();
        map.put("BeginDate", beginDateAndEndDate.get("BeginDate").toString());
        map.put("EndDate", beginDateAndEndDate.get("EndDate").toString());
        map.put("CampusID", CampusID);
        map.put("cField1", cField1);
        map.put("BeginDate", beginDateAndEndDate.get("BeginDate").toString());

        HSSFWorkbook workbook = new HSSFWorkbook();
        //生成一个表格
        HSSFSheet sheet = workbook.createSheet("MKT");
        HSSFSheet sheetRef = workbook.createSheet("REF");
        HSSFSheet sheetReNew = workbook.createSheet("ReNew");

        //设置表格默认列宽度为15个字节
        sheet.setDefaultColumnWidth((short) 15);
        sheetRef.setDefaultColumnWidth((short) 15);
        sheetReNew.setDefaultColumnWidth((short) 15);

        //产生表格标题行
        HSSFRow row = sheet.createRow(0);
        HSSFRow rowRef = sheetRef.createRow(0);
        HSSFRow rowNeNew = sheetReNew.createRow(0);

        HSSFCell cell = row.createCell(0);
        HSSFCell cell1 = row.createCell(1);
        HSSFCell cell2 = row.createCell(2);
        HSSFCell cell3 = row.createCell(3);
        HSSFCell cell4 = row.createCell(4);

        HSSFCell cellRef = rowRef.createCell(0);
        HSSFCell cellRef1 = rowRef.createCell(1);
        HSSFCell cellRef2 = rowRef.createCell(2);
        HSSFCell cellRef3 = rowRef.createCell(3);
        HSSFCell cellRef4 = rowRef.createCell(4);

        HSSFCell cellNeNew = rowNeNew.createCell(0);
        HSSFCell cellNeNew1 = rowNeNew.createCell(1);
        HSSFCell cellNeNew2= rowNeNew.createCell(2);
        HSSFCell cellNeNew3= rowNeNew.createCell(3);
        HSSFCell cellNeNew4 = rowNeNew.createCell(4);
        HSSFCell cellNeNew5 = rowNeNew.createCell(5);

        cell.setCellValue("单据号");
        cell1.setCellValue("客户名");
        cell2.setCellValue("客户手机号");
        cell3.setCellValue("MKT业绩");
        cell4.setCellValue("业绩归属日期");

        cellRef.setCellValue("单据号");
        cellRef1.setCellValue("客户名");
        cellRef2.setCellValue("客户手机号");
        cellRef3.setCellValue("REF业绩");
        cellRef4.setCellValue("业绩归属日期");

        cellNeNew.setCellValue("单据号");
        cellNeNew1.setCellValue("客户名");
        cellNeNew2.setCellValue("客户手机号");
        cellNeNew3.setCellValue("ReNew业绩");
        cellNeNew4.setCellValue("ReNew提前续费业绩");
        cellNeNew5.setCellValue("业绩归属日期");

        String cName = "";

        map.put("SaleModeIds",MKT);
        List detail = new ArrayList();
        if(Integer.valueOf(AutoID) > 8){
            detail = campusDsrNewService.queryDsrByUserSummaryDetailNew(map);
        }else {
            detail = campusDsrNewService.queryDsrByUserSummaryDetail(map);
        }

        map.put("SaleModeIds",REF);
        List detailREF = new ArrayList();
        if(Integer.valueOf(AutoID) > 8){
            detailREF = campusDsrNewService.queryDsrByUserSummaryDetailNew(map);
        }else {
            detailREF = campusDsrNewService.queryDsrByUserSummaryDetail(map);
        }


        List detailReNew = campusDsrNewService.queryDsrByUserSummaryDetailByReNew(map);

        if (detail.size() > 0) {
            for (int i = 0; i < detail.size(); i++) {
                HSSFRow rowValue = sheet.createRow(1 + i);
                HSSFCell cellDate = rowValue.createCell(0);
                HSSFCell cellDate1 = rowValue.createCell(1);
                HSSFCell cellDate2 = rowValue.createCell(2);
                HSSFCell cellDate3 = rowValue.createCell(3);
                HSSFCell cellDate4 = rowValue.createCell(4);

                Map appMap = (HashMap) detail.get(i);
                cName = appMap.get("cName").toString();

                cellDate.setCellValue(appMap.get("cReceiptNo").toString());
                cellDate1.setCellValue(appMap.get("ctmName").toString());
                cellDate2.setCellValue(appMap.get("cSMSTel").toString());
                cellDate3.setCellValue(appMap.get("En").toString());
                cellDate4.setCellValue(appMap.get("cEffectDate").toString());
            }
        }

        if (detailREF.size() > 0) {
            for (int i = 0; i < detailREF.size(); i++) {
                HSSFRow rowValue = sheetRef.createRow(1 + i);
                HSSFCell cellDate = rowValue.createCell(0);
                HSSFCell cellDate1 = rowValue.createCell(1);
                HSSFCell cellDate2 = rowValue.createCell(2);
                HSSFCell cellDate3 = rowValue.createCell(3);
                HSSFCell cellDate4 = rowValue.createCell(4);

                Map appMap = (HashMap) detailREF.get(i);
                cName = appMap.get("cName").toString();

                cellDate.setCellValue(appMap.get("cReceiptNo").toString());
                cellDate1.setCellValue(appMap.get("ctmName").toString());
                cellDate2.setCellValue(appMap.get("cSMSTel").toString());
                cellDate3.setCellValue(appMap.get("En").toString());
                cellDate4.setCellValue(appMap.get("cEffectDate").toString());
            }
        }

        if (detailReNew.size() > 0) {
            for (int i = 0; i < detailReNew.size(); i++) {
                HSSFRow rowValue = sheetReNew.createRow(1 + i);
                HSSFCell cellDate = rowValue.createCell(0);
                HSSFCell cellDate1 = rowValue.createCell(1);
                HSSFCell cellDate2 = rowValue.createCell(2);
                HSSFCell cellDate3 = rowValue.createCell(3);
                HSSFCell cellDate4 = rowValue.createCell(4);
                HSSFCell cellDate5 = rowValue.createCell(5);

                Map appMap = (HashMap) detailReNew.get(i);
                cName = appMap.get("userName").toString();

                cellDate.setCellValue(appMap.get("cReceiptNo").toString());
                cellDate1.setCellValue(appMap.get("cName").toString());
                cellDate2.setCellValue(appMap.get("cSMSTel").toString());
                cellDate3.setCellValue(appMap.get("Contracts").toString());
                cellDate4.setCellValue(appMap.get("Contracts2").toString());
                cellDate5.setCellValue(appMap.get("cEffectDate").toString());
            }
        }

        ServletOutputStream outputStream = null;// 创建一个输出流对象
        try {
            response.setContentType("application/binary;charset=utf-8");
            String headerStr = new String((cName+"业绩明细").getBytes("utf-8"), "ISO8859-1");// headerString为中文时转码
            response.setHeader("Content-disposition", "attachment; filename=" + headerStr + ".xls");// filename是下载的xls的名
            outputStream = response.getOutputStream();
            workbook.write(outputStream);
            outputStream.flush();
            outputStream.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
}
